1325 min read

Time-series and yfinance api

2020-07-07

Python integration using the reticulate package.

yFinance api from https://pypi.org/project/yfinance/

Python exchange rate api from www.exchangerate-api.com

library(reticulate)
library(tidyquant)
library(timetk)
library(sweep)
library(forecast)
library(tidyverse)
library(bbplot)
library(lubridate)
library(Mcomp)
library(smooth)
library(broom)
library(plotly)
library(readxl)

py_config()
## python:         C:/Users/Roy/AppData/Local/r-miniconda/envs/r-reticulate/python.exe
## libpython:      C:/Users/Roy/AppData/Local/r-miniconda/envs/r-reticulate/python36.dll
## pythonhome:     C:/Users/Roy/AppData/Local/r-miniconda/envs/r-reticulate
## version:        3.6.10 (default, Mar  5 2020, 10:17:47) [MSC v.1900 64 bit (AMD64)]
## Architecture:   64bit
## numpy:          C:/Users/Roy/AppData/Local/r-miniconda/envs/r-reticulate/Lib/site-packages/numpy
## numpy_version:  1.18.5
use_miniconda()
import yfinance as yf


def yfinance01(ticker,period):
    g = yf.Ticker(ticker)
    data=g.history(period=period)
    return data
fixcol01 <- function(g){
  
  g <- cbind(date = rownames(g), g)
  rownames(g) <- 1:nrow(g)
  return(g)
}

retrieve01 <- function(ticker,period="max"){
  g <- py$yfinance01(ticker,period)
  g <- fixcol01(g)
  g <- g %>% 
    select(date, Close)%>% 
    mutate(date=as_date(ymd(date)))
  colnames(g) <- c("date", ticker)
  return(g)
}


retrieve02 <- function(ticker,period="max"){
  g <- py$yfinance01(ticker,period)
  
  return(g)
}



plotting01 <- function(ticker, period="max"){
  g <- retrieve01(ticker, period)
  plot <- g %>%  
    ggplot(aes(date, get(ticker), group=1))+
    geom_point(shape=".")+
    labs(title=ticker)+
    theme_void()+
    scale_y_continuous()+
    theme(axis.text = element_text())+
    geom_line(size=1)
  return(plot)
}

multiplot <- function(..., period = "max",length=1000){
  x <- list(...)
  y <- data.frame("date"=c(seq(today()-length,by="day",length.out = length)))
  for (val in x){
    #y <- left_join(y, retrieve01(val, period),by=c("date"))
    y <- merge(y, retrieve01(val, period), by = "date", allow.cartesian = TRUE)
  }
  h <- y %>%
    pivot_longer(cols = c(...),names_to = "ticker",values_to = "price")
  return(h)
}

Retail industry

M&S Morrisons Tesco Walmart

(data <- multiplot("WMT","MRW.L","TSCO.L","MKS.L", period = "max",length = 12000) %>% 
  filter(!is.na(price)) %>% 
  ggplot()+
  geom_line(aes(date, price, colour = ticker))+
  theme_void()+
  labs(colour = "Ticker",
       title="Retail industry - 30 year overview")+
  theme(axis.text = element_text(),
        legend.position = "bottom"))

(data <- multiplot("WMT","MRW.L","TSCO.L","MKS.L", period = "max",length = 3650) %>% 
  filter(!is.na(price)) %>% 
  ggplot()+
  geom_line(aes(date, price, colour = ticker))+
  geom_smooth(colour = "black", lty = 2, aes(date, price), se = F)+
  theme_void()+
  labs(colour = "Ticker",
       title = "Retail industry - 10 year overview")+
  theme(axis.text = element_text(),
        legend.position = "bottom"))

taking log prices

(data <- multiplot("WMT","MRW.L","TSCO.L","MKS.L", period = "max",length = 3650) %>% 
  filter(!is.na(price)) %>% 
  mutate(price=log(price)) %>% 
  ggplot()+
  geom_point(aes(date, price, colour = ticker), shape=".")+
  geom_smooth(aes(date, price, colour = ticker), se = F)+
  stat_smooth(aes(date, price),se = F,geom="line",lty=4)+
  theme_void()+
  labs(colour = "Ticker")+
  theme(axis.text = element_text(),
        legend.position = "bottom"))

(data <- multiplot("WMT","MRW.L","TSCO.L","MKS.L", period = "max",length = 15000) %>% 
  filter(!is.na(price),
         price>1) %>% 
  mutate(price=log(price)) %>% 
  ggplot()+
  geom_line(aes(date, price, colour = ticker))+
  geom_smooth(colour = "black", lty = 2, aes(date, price), se = F)+
  geom_smooth(aes(date, price, colour = ticker),se = F)+
  theme_void()+
  labs(colour = "Ticker")+
  theme(axis.text = element_text(),
        legend.position = "bottom"))

ggplotly(data)

Car industry financial analysis

A profitability decomposition analysis with a focus on Tesla (TSLA)

(data <- multiplot("TSLA","HYMTF", "FCAU","RACE","NSANY","F","BMW.MI","BMW.DE","HMC","GM","VOW.DE","TM", period = "max",length = 1825) %>% 
  filter(!is.na(price),
         price>1) %>%
  mutate(price=log(price)) %>% 
  ggplot()+
  geom_line(aes(date, price, colour = ticker))+
  geom_smooth(aes(date, price, colour = ticker),se = F)+
  geom_smooth(colour = "black", lty = 2, aes(date, price), se = F)+
  theme_void()+
  labs(colour = "Ticker",
       title="Share prices of global car companies (5year)")+
  theme(axis.text = element_text(),
        legend.position = "bottom"))

decomp <- function(x) {
  data <- read_excel(x, sheet = 3,col_names = T)
  namesheet <- read_excel(x, sheet = 1,col_names = FALSE)
  
  data.frame(
    "id" = c(1:5),
    "ROCE" = c(
      paste(as.numeric(data[7, 3])),
      paste(as.numeric(data[7, 4])),
      paste(as.numeric(data[7, 5])),
      paste(as.numeric(data[7, 6])),
      paste(as.numeric(data[7, 7]))
    ),
    "year" = c(
      paste(data[4, 3]),
      paste(data[4, 4]),
      paste(data[4, 5]),
      paste(data[4, 6]),
      paste(data[4, 7])
    ),
    company = paste(namesheet[1, 1]),
    "RNOA" = c(
      paste(as.numeric(data[9, 3])),
      paste(as.numeric(data[9, 4])),
      paste(as.numeric(data[9, 5])),
      paste(as.numeric(data[9, 6])),
      paste(as.numeric(data[9, 7]))
    ),
    "FLEV" = c(
      paste(as.numeric(data[10, 3])),
      paste(as.numeric(data[10, 4])),
      paste(as.numeric(data[10, 5])),
      paste(as.numeric(data[10, 6])),
      paste(as.numeric(data[10, 7]))
    ),
    "NBC" = c(
      paste(as.numeric(data[11, 3])),
      paste(as.numeric(data[11, 4])),
      paste(as.numeric(data[11, 5])),
      paste(as.numeric(data[11, 6])),
      paste(as.numeric(data[11, 7]))
    ),
    "spread" = c(
      paste(as.numeric(data[12, 3])),
      paste(as.numeric(data[12, 4])),
      paste(as.numeric(data[12, 5])),
      paste(as.numeric(data[12, 6])),
      paste(as.numeric(data[12, 7]))
    ),
    "PM" = c(
      paste(as.numeric(data[15, 3])),
      paste(as.numeric(data[15, 4])),
      paste(as.numeric(data[15, 5])),
      paste(as.numeric(data[15, 6])),
      paste(as.numeric(data[15, 7]))
    ),
    "ATO" = c(
      paste(as.numeric(data[16, 3])),
      paste(as.numeric(data[16, 4])),
      paste(as.numeric(data[16, 5])),
      paste(as.numeric(data[16, 6])),
      paste(as.numeric(data[16, 7]))
    ),
    "GPM" = c(
      paste(as.numeric(data[20, 3])),
      paste(as.numeric(data[20, 4])),
      paste(as.numeric(data[20, 5])),
      paste(as.numeric(data[20, 6])),
      paste(as.numeric(data[20, 7]))
    ),
    "operating expense over sales" = c(
      paste(as.numeric(data[21, 3])),
      paste(as.numeric(data[21, 4])),
      paste(as.numeric(data[21, 5])),
      paste(as.numeric(data[21, 6])),
      paste(as.numeric(data[21, 7]))
    ),
    "taxes_over_sales" = c(
      paste(as.numeric(data[22, 3])),
      paste(as.numeric(data[22, 4])),
      paste(as.numeric(data[22, 5])),
      paste(as.numeric(data[22, 6])),
      paste(as.numeric(data[22, 7]))
    ),
    "other_income_over_sales" = c(
      paste(as.numeric(data[23, 3])),
      paste(as.numeric(data[23, 4])),
      paste(as.numeric(data[23, 5])),
      paste(as.numeric(data[23, 6])),
      paste(as.numeric(data[23, 7]))
    ),
    "one_over_ATO" = c(
      paste(as.numeric(data[26, 3])),
      paste(as.numeric(data[26, 4])),
      paste(as.numeric(data[26, 5])),
      paste(as.numeric(data[26, 6])),
      paste(as.numeric(data[26, 7]))
    ),
    "cash over sales" = c(
      paste(as.numeric(data[27, 3])),
      paste(as.numeric(data[27, 4])),
      paste(as.numeric(data[27, 5])),
      paste(as.numeric(data[27, 6])),
      paste(as.numeric(data[27, 7]))
    ),
    "receivables over sales" = c(
      paste(as.numeric(data[28, 3])),
      paste(as.numeric(data[28, 4])),
      paste(as.numeric(data[28, 5])),
      paste(as.numeric(data[28, 6])),
      paste(as.numeric(data[28, 7]))
    ),
    "inventory_over_sales" = c(
      paste(as.numeric(data[29, 3])),
      paste(as.numeric(data[29, 4])),
      paste(as.numeric(data[29, 5])),
      paste(as.numeric(data[29, 6])),
      paste(as.numeric(data[29, 7]))
    ),
    "other_current_assets_over_sales" = c(
      paste(as.numeric(data[30, 3])),
      paste(as.numeric(data[30, 4])),
      paste(as.numeric(data[30, 5])),
      paste(as.numeric(data[30, 6])),
      paste(as.numeric(data[30, 7]))
    ),
    "PPE_over_sales" = c(
      paste(as.numeric(data[31, 3])),
      paste(as.numeric(data[31, 4])),
      paste(as.numeric(data[31, 5])),
      paste(as.numeric(data[31, 6])),
      paste(as.numeric(data[31, 7]))
    ),
    "accounts_payable_over_sales" = c(
      paste(as.numeric(data[33, 3])),
      paste(as.numeric(data[33, 4])),
      paste(as.numeric(data[33, 5])),
      paste(as.numeric(data[33, 6])),
      paste(as.numeric(data[33, 7]))
    ),
    "income_taxes_over_sales" = c(
      paste(as.numeric(data[34, 3])),
      paste(as.numeric(data[34, 4])),
      paste(as.numeric(data[34, 5])),
      paste(as.numeric(data[34, 6])),
      paste(as.numeric(data[34, 7]))
    ),
    "other_liabilities_over_sales" = c(
      paste(as.numeric(data[35, 3])),
      paste(as.numeric(data[35, 4])),
      paste(as.numeric(data[35, 5])),
      paste(as.numeric(data[35, 6])),
      paste(as.numeric(data[35, 7]))
    )
  )
}
profitability <- decomp("Decompositions\\Ferrari.xlsx") %>% 
  union_all(decomp("Decompositions\\Fiat Chrysler.xlsx")) %>% 
  union_all(decomp("Decompositions\\Ford.xlsx")) %>% 
  union_all(decomp("Decompositions\\GM.xlsx")) %>% 
  union_all(decomp("Decompositions\\Honda.xlsx")) %>% 
  union_all(decomp("Decompositions\\Hyundai.xlsx")) %>% 
  union_all(decomp("Decompositions\\Nissan.xlsx")) %>% 
  union_all(decomp("Decompositions\\Tesla.xlsx")) %>% 
  union_all(decomp("Decompositions\\Toyota.xlsx")) %>% 
  union_all(decomp("Decompositions\\Volkswagen.xlsx")) %>% 
  union_all(decomp("Decompositions\\BMW.xlsx")) %>% 
  union_all(decomp("Decompositions\\Daimler.xlsx")) %>% 
  mutate(ROCE=as.numeric(ROCE),
         RNOA=as.numeric(RNOA),
         FLEV=as.numeric(FLEV),
         NBC=as.numeric(NBC),
         spread=as.numeric(spread),
         PM=as.numeric(PM),
         ATO=as.numeric(ATO),
         GPM=as.numeric(GPM),
         operating.expense.over.sales=as.numeric(operating.expense.over.sales),
         taxes_over_sales=as.numeric(taxes_over_sales),
         other_income_over_sales=as.numeric(other_income_over_sales),
         one_over_ATO=as.numeric(one_over_ATO),
         cash.over.sales=as.numeric(cash.over.sales),
         receivables.over.sales=as.numeric(receivables.over.sales),
         inventory_over_sales=as.numeric(inventory_over_sales),
         other_current_assets_over_sales=as.numeric(other_current_assets_over_sales),
         PPE_over_sales=as.numeric(PPE_over_sales),
         accounts_payable_over_sales=as.numeric(accounts_payable_over_sales),
         income_taxes_over_sales=as.numeric(income_taxes_over_sales),
         other_liabilities_over_sales=as.numeric(other_liabilities_over_sales)) %>% 
  mutate(name=case_when(str_detect(company, "BMW") ~ "BMW",
                        str_detect(company, "Daim") ~ "Daimler",
                        str_detect(company, "FORD") ~ "Ford",
                        str_detect(company, "GENERAL") ~ "GM",
                        str_detect(company, "Ferrar") ~ "Ferrari",
                        str_detect(company, "Fiat") ~ "Fiat Chrysler",
                        str_detect(company, "HONDA") ~ "Honda",
                        str_detect(company, "HYUNDAI") ~ "Hyundai",
                        str_detect(company, "NISSAN") ~ "Nissan",
                        str_detect(company, "TESLA") ~ "Tesla",
                        str_detect(company, "TOYOTA") ~ "Toyota",
                        str_detect(company, "Volkswagen") ~ "Volkswagen"),
         period=case_when(id==5 ~ 2015,
                          id==4 ~ 2016,
                          id==3 ~ 2017,
                          id==2 ~ 2018,
                          id==1 ~ 2019))

Investigating industry ROCE

Ferrari has a wildy changing ROCE value over the investigation period so will have to be examined separately.

investigate01 <- function(data, y,title="",subtitle=""){
  g <- ggplot(data,aes(period,y,colour=name))+
  geom_smooth(aes(period,y,group=1),lty=2,colour="black",fill="green",alpha=0.1)+
  geom_point()+
    stat_smooth(geom = "line",lty=2,method = "lm",se=F)+
    geom_smooth(se=F)+
  theme_void()+
  labs(title=title,
       subtitle = subtitle,
       colour="Company")+
  theme(axis.text = element_text()) 
  return(g)
}

investigate01(profitability,profitability$ROCE)

Having removed Ferrari, the data points look to be reasonably distributed around the average. Tesla looks to be consistently under performing within the industry as a measure of ROCE.

profit_roce_no_outlier <- profitability %>% 
      filter(!str_detect(name,"Ferrari"))

ggplotly(investigate01(profit_roce_no_outlier,profit_roce_no_outlier$ROCE,title="Industry return on capital employed (ROCE)"))

ROCE = RNOA + FLEV(RNOA - NBC)

ROCE = (PM - ATO) + FLEV(RNOA - NBC)

ROCE = (GM ratio + OE ratio + tax ratio + OI ratio) - ((1/ATO + cash ratio + inventory ratio + other curr. assets ratio + PPE ratio) - (payables ratio + income tax ratio + other liabilities ratio)) + FLEV(RNOA - NBC)

First level decomposition

RNOA

investigate01(profitability,profitability$RNOA)

ggplotly(investigate01(profitability,profitability$RNOA, title="Industry return on net assets (RNOA)"))

FLEV

investigate01(profitability,profitability$FLEV)

ggplotly(investigate01(profitability,profitability$FLEV, title="Industry financial leverage (FLEV)"))

ignoring Ferrari in FLEV

investigate01(profit_roce_no_outlier,profit_roce_no_outlier$FLEV)

ggplotly(investigate01(profit_roce_no_outlier,profit_roce_no_outlier$FLEV, title="Industry financial leverage (FLEV, no Ferrari)"))

NBC

investigate01(profitability,profitability$FLEV)

ggplotly(investigate01(profitability,profitability$FLEV, title="Industry financial leverage (FLEV)"))

NBC without ferrari

investigate01(profit_roce_no_outlier,profit_roce_no_outlier$NBC)

ggplotly(investigate01(profit_roce_no_outlier,profit_roce_no_outlier$NBC, title="Industry financial leverage (FLEV, no Ferrari)"))

Spread

investigate01(profitability,profitability$spread, title="Industry spread (RNOA - NBC)")

ggplotly(investigate01(profitability,profitability$spread, title="Industry spread (RNOA - NBC)"))

Second level decomposition RNOA = PM - ATO

PM

investigate01(profitability,profitability$PM, title="Industry profit margin (OI / sales)")

ggplotly(investigate01(profitability,profitability$PM, title="Industry profit margin (OI / sales)"))

ATO

investigate01(profitability,profitability$ATO, title="Industry asset turnover ratio (sales / NOA)")

ggplotly(investigate01(profitability,profitability$ATO, title="Industry asset turnover ratio (sales / NOA)"))

Fiat Chrysler has an unusually high asset turnover ratio.

Third level decomposition

Ignoring ferrari

investigate01(profit_roce_no_outlier,profit_roce_no_outlier$GPM, title="Industry gross margin ratio (GM / sales)")

ggplotly(investigate01(profit_roce_no_outlier,profit_roce_no_outlier$GPM, title="Industry gross margin ratio (GM / sales)"))